#region Usings

using System.Collections.Generic;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Karma.Framework.Core.Domain.Tools.Import.Configuration;
using log4net;
using Karma.Framework.Core.Domain.Tools.Import.Layout;

#endregion

namespace Karma.Framework.Core.Domain.Tools.Import.Validation
{
    public class ExcelLayoutValidator : ILayoutValidator
    {
        private static readonly ILog LOG = LogManager.GetLogger(typeof (ExcelLayoutValidator).Name);

        private readonly string[] ColumnNames = new[]
                                                    {
                                                        "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M",
                                                        "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
                                                        "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK"
                                                        , "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU",
                                                        "AV", "AX", "AY", "AZ",
                                                        "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK"
                                                        , "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU",
                                                        "BV", "BW", "BX", "BY", "BZ",
                                                        "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK"
                                                        , "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU",
                                                        "CV", "CW", "CX", "CY", "CZ",
                                                        "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK"
                                                        , "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU",
                                                        "DV", "DW", "DX", "DY", "DZ",
                                                        "EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK"
                                                        , "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU",
                                                        "EV", "EW", "EX", "EY", "EZ"
                                                    };

        #region ILayoutValidator Members

        public ValidationSummary Validate(EntityLayout layout, Stream dataFile)
        {
            var eConf = (from e in ImporterConfiguration.ImportableEntities
                         where e.Type.Equals(layout.Entity)
                         select e).First();
            using (var spreadsheet = SpreadsheetDocument.Open(dataFile, false))
            {
                if (LOG.IsDebugEnabled)
                {
                    LOG.Debug(string.Format("Validating Layout for entity {0}", eConf.Alias));
                }
                var summary = new ValidationSummary();
                var sharedStrings = spreadsheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();

                var sheet = (from s in spreadsheet.WorkbookPart.Workbook.Descendants<Sheet>()
                             where s.Name == eConf.Alias
                             select s).Single();
                summary.ErrorSummary =
                    ValidateEntityLayout((WorksheetPart) spreadsheet.WorkbookPart.GetPartById(sheet.Id),
                                         sharedStrings, layout);

                // agregados
                foreach (var agregado in layout.Aggregates)
                {
                    sheet = (from s in spreadsheet.WorkbookPart.Workbook.Descendants<Sheet>()
                             where s.Name == agregado.Entity.Name
                             select s).SingleOrDefault();
                    var errors = ValidateEntityLayout(
                        (WorksheetPart) spreadsheet.WorkbookPart.GetPartById(sheet.Id),
                        sharedStrings, agregado);
                    if (errors != null)
                    {
                        foreach (var error in errors.Errors)
                        {
                            var errorSummary = summary.ErrorSummary;
                            AddErrorToSummary(ref errorSummary, error);
                            if (summary.ErrorSummary == null)
                            {
                                summary.ErrorSummary = errorSummary;
                            }
                        }
                    }
                }
                if (LOG.IsDebugEnabled)
                {
                    LOG.Debug("Layout validated.");
                }
                return summary;
            }
        }

        #endregion

        private ErrorSummary ValidateEntityLayout(WorksheetPart worksheetPart, SharedStringTablePart sharedStrings,
                                                  EntityLayout entityLayout)
        {
            if (worksheetPart == null)
            {
                return null;
            }
            ErrorSummary summary = null;
            var rows = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
            /* check the headers */
            if (LOG.IsDebugEnabled)
            {
                LOG.Debug("Validate headers...");
            }
            var headerRow = rows.ElementAt(0);
            var headerRowCells = headerRow.Elements<Cell>();
            // id
            var offset = entityLayout is AggregateLayout ? 0 : 1;
            if (!(entityLayout is AggregateLayout))
            {
                var cell = headerRowCells.ElementAtOrDefault(0);
                var field = entityLayout.PrimaryKey;
                var val = cell == null ? null : GetStringFromCell(cell, sharedStrings);
                if (!field.Name.Equals(val))
                {
                    AddErrorToSummary(ref summary, new ErrorDescription
                                                       {
                                                           Line = 1,
                                                           Column = 1,
                                                           Property = field,
                                                           Type = ErrorType.Format,
                                                           Severity = ErrorSeverity.Fatal,
                                                           Description =
                                                               string.Format(
                                                               "Columna incorrecta {0}, se esperaba la columna {1}.",
                                                               val, field.Name)
                                                       });
                }
            }
            // rest of the headers
            for (var i = 0; i < entityLayout.Properties.Count; i++)
            {
                var cell = headerRowCells.ElementAtOrDefault(i + offset);
                var field = entityLayout.Properties[i];
                var fieldName = "";
                if (field is ReferenceField)
                {
                    var refField = (ReferenceField) field;
                    fieldName = refField.GetName();
                }
                else
                {
                    var propField = (PropertyField) field;
                    fieldName = propField.Name;
                }
                var val = cell == null ? null : GetStringFromCell(cell, sharedStrings);
                if (!fieldName.Equals(val))
                {
                    AddErrorToSummary(ref summary, new ErrorDescription
                                                       {
                                                           Line = 1,
                                                           Column = i + 1 + offset,
                                                           Property = field,
                                                           Type = ErrorType.Format,
                                                           Severity = ErrorSeverity.Fatal,
                                                           Description =
                                                               string.Format(
                                                               "Columna incorrecta {0}, se esperaba la columna {1}.",
                                                               val, field.Name)
                                                       });
                }
            }
            /* si existen columnas desfazadas no continues */
            if (summary != null)
            {
                return summary;
            }
            if (LOG.IsDebugEnabled)
            {
                LOG.Debug("Headers are valid, check the data.");
            }
            /* check the data */
            var rowCount = rows.Count();
            for (var i = 1; i < rowCount; i++)
            {
                var row = rows.ElementAt(i);
                var cells = row.Elements<Cell>();
                // read properties
                for (var j = 0; j < entityLayout.Properties.Count; j++)
                {
                    var cellReference = ColumnNames[j + offset] + (i + 1);
                    var cell = (from c in cells
                                where c.CellReference == cellReference
                                select c).SingleOrDefault();
                    var field = entityLayout.Properties[j];
                    var val = cell == null ? null : GetStringFromCell(cell, sharedStrings);
                    if (!field.Nullable && string.IsNullOrEmpty(val))
                    {
                        AddErrorToSummary(ref summary, new ErrorDescription
                                                           {
                                                               Line = i + 1,
                                                               Column = j + 1 + offset,
                                                               Property = field,
                                                               Type = ErrorType.Format,
                                                               Severity = ErrorSeverity.Error,
                                                               Description =
                                                                   string.Format(
                                                                   "La columna {0} no acepta valores vacios.",
                                                                   field.Name)
                                                           });
                    }
                }
            }
            return summary;
        }

        private void AddErrorToSummary(ref ErrorSummary summary, ErrorDescription error)
        {
            if (summary == null)
            {
                summary = new ErrorSummary();
                summary.Errors = new List<ErrorDescription>();
            }
            if (LOG.IsDebugEnabled)
            {
                LOG.Debug(string.Format("Error found: line[{0}], Column[{1}], Property[{2}], Description[{3}]",
                                        error.Line,
                                        error.Column, error.Property.Name, error.Description));
            }
            summary.Errors.Add(error);
        }

        private string GetStringFromCell(Cell cell, SharedStringTablePart sharedStrings)
        {
            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                var stringId = int.Parse(cell.CellValue.Text);
                var sharedstring = sharedStrings.SharedStringTable.Elements<SharedStringItem>().ElementAt(
                    stringId);
                return sharedstring.InnerText;
            }
            return cell.CellValue != null ? cell.CellValue.Text : "";
        }
    }
}